/**
 * Now the logic of the main table is as follows:
 * If the value of the rank for the page is new or different from the previous one,
 * 2 values are inserted, the second one is 1s fresher then the first one.
 * If we test the same page again and get the same rank, we can just update the last
 * one, to make the time-span of the rank longer and not waste the database space.
 * 
 */
EXPORTED_SYMBOLS = [
	"seopdb"
];

var consoleService = Components.classes["@mozilla.org/consoleservice;1"].getService(Components.interfaces.nsIConsoleService);
LOG = function(msg) {
	consoleService.logStringMessage("seop: " + msg);
}

var seopdb = {
conn: null,
init: function() {
	var file = Components.classes["@mozilla.org/file/directory_service;1"]
                     .getService(Components.interfaces.nsIProperties)
                     .get("ProfD", Components.interfaces.nsIFile);
	file.append("seop.sqlite");

	var storageService = Components.classes["@mozilla.org/storage/service;1"]
                        .getService(Components.interfaces.mozIStorageService);
	var createdb = 0;
	try {
		this.conn = storageService.openDatabase(file);
		// check whether we have got the tables
		var statement = this.conn.createStatement("SELECT count(*) FROM sqlite_master WHERE type='table' and name='whitelist' ");
		while (statement.executeStep()) {
			var value = statement.getInt32(0);
			if (!value) {
				// initialize the database
				createdb = 1;
			}
		}
		statement.finalize();
	} catch(e) {
		LOG("Exception: " + e);
		createdb = 1;
		file.remove(false);
		this.conn = storageService.openDatabase(file);
	}
	if (createdb) {
		this.createdb();
	}
},
createdb: function() {
	LOG("Creating database");
	this.conn.executeSimpleSQL("CREATE TABLE whitelist (url TEXT PRIMARY KEY)");
	this.conn.executeSimpleSQL("CREATE TABLE urls (uid INTEGER PRIMARY KEY, url TEXT)");
	this.conn.executeSimpleSQL("CREATE TABLE ranks (uid INTEGER, rank INTEGER, tm INTEGER, value REAL)");
	LOG("Creating database - done");
},
/*
 * Adds a record to the whitelist.
 */
whitelist: function(head) {
	var s1 = this.conn.createStatement("INSERT INTO whitelist (url) VALUES (?1)");
	s1.bindStringParameter(0, head);
	s1.execute();
	s1.finalize();
},
is_whitelisted: function(url) {
	var longest = "";
	var s1 = this.conn.createStatement("SELECT url FROM whitelist WHERE url=substr(?1, 1, length(url))");
	s1.bindStringParameter(0, url);
	while (s1.executeStep()) {
		var s = s1.getString(0);
		if (s.length>longest.length) {
			longest = s;
		}
	}
	s1.finalize();
	return longest;
},
un_whitelist: function(url) {
	var longest = "";
	var s1 = this.conn.createStatement("SELECT url FROM whitelist WHERE url=substr(?1, 1, length(url))");
	s1.bindStringParameter(0, url);
	while (s1.executeStep()) {
		var s = s1.getString(0);
		if (s.length>longest.length) {
			longest = s;
		}
	}
	s1.finalize();
	if (""!=longest) {
		var s1 = this.conn.createStatement("DELETE FROM whitelist WHERE url=?1");
		s1.bindStringParameter(0, longest);
		s1.execute();
		s1.finalize();
		LOG("Deleted " + longest);
	}
},
add_rank: function(url, rank, val) {
	if (""==this.is_whitelisted(url)) {
		LOG("add_rank: ignoring not whitelisted " + url);
		return ;
	}
	var uid = null;
	var s1 = this.conn.createStatement("SELECT uid FROM urls WHERE url=?1");
	s1.bindStringParameter(0, url);
	while (s1.executeStep()) {
		uid = s1.getInt32(0);
	}
	var insert = 1;
	s1.finalize();
	var row_id = null;
	var last_value = null;
	if (null==uid) {
		// insert
		s1 = this.conn.createStatement("INSERT INTO urls (url) VALUES (?1)");
		s1.bindStringParameter(0, url);
		s1.execute();
		s1.finalize();
		uid = this.conn.lastInsertRowID;
	} else {
		// was the last one the same as the one being inserted? Then just update the time.
		s1 = this.conn.createStatement("SELECT rowid, value FROM ranks WHERE uid=?1 AND rank=?2 ORDER BY tm DESC LIMIT 1");
		s1.bindInt32Parameter(0, uid);
		s1.bindInt32Parameter(1, rank);
		while (s1.executeStep()) {
			row_id = s1.getInt64(0);
			last_value = s1.getDouble(1);
		}
		s1.finalize();
		if (null!=last_value && last_value==val) {
			insert = 0;
			// and row_id is valid
		}
	}
	if (insert) {
		var dt = (new Date()).getTime();
		s1 = this.conn.createStatement("INSERT INTO ranks VALUES (?1, ?2, ?3, ?4)");
		s1.bindInt32Parameter(0, uid);
		s1.bindInt32Parameter(1, rank);
		s1.bindInt64Parameter(2, dt);
		s1.bindDoubleParameter(3, val);
		s1.execute();
		s1.bindInt32Parameter(0, uid);
		s1.bindInt32Parameter(1, rank);
		s1.bindInt64Parameter(2, dt+1);
		s1.bindDoubleParameter(3, val);
		s1.execute();
		s1.finalize();
	} else {
		var dt = (new Date()).getTime();
		s1 = this.conn.createStatement("UPDATE ranks SET tm=?1 WHERE rowid=?2");
		s1.bindInt64Parameter(0, dt);
		s1.bindInt64Parameter(1, row_id);
		s1.execute();
		s1.finalize();
	}
},
get_rank: function(url, rank) {
	var ret = null;
	var s1 = this.conn.createStatement(
		"SELECT ranks.value, ranks.tm FROM ranks, urls " + 
		"WHERE ranks.uid=urls.uid AND ranks.rank=?1 AND urls.url=?2 " + 
		"ORDER BY tm DESC LIMIT 1");
	s1.bindInt32Parameter(0, rank);
	s1.bindStringParameter(1, url);
	if (s1.executeStep()) {
		var tm = new Date();
		tm.setTime(s1.getInt64(1));
		ret = [ s1.getDouble(0), tm ];
	}
	s1.finalize();
	return ret;
},
get_tl: function(url, rank) {
	var ret = [];
	var s1 = this.conn.createStatement(
		"SELECT ranks.value, ranks.tm FROM ranks, urls " + 
		"WHERE ranks.uid=urls.uid AND ranks.rank=?1 AND urls.url=?2 " + 
		"ORDER BY tm");
	s1.bindInt32Parameter(0, rank);
	s1.bindStringParameter(1, url);
	while (s1.executeStep()) {
		var val = s1.getDouble(0);
		var tm = new Date();
		tm.setTime(s1.getInt64(1));
		tm.setSeconds(0);
		tm.setMinutes(0);
		tm.setHours(10);
		tm.setMilliseconds(0);
		ret.push([ tm, val]);
	}
	s1.finalize();
	return ret;
},
__sentinel: 0 //so that we can write a , after every bloody member.
};

seopdb.init();

